from datetime import datetime
from typing import List, Dict, Any, Optional

from sqlalchemy.orm import Session
import models
from utils import SITE_STATUS_DICT, SITE_CAT_DICT


def get_site_statistics(db: Session, cluster_id: Optional[str] = None, site_status: Optional[int] = None):
    """获取站点统计信息"""
    query = db.query(models.GatewaySiteConfig)

    # 添加群组过滤条件
    if cluster_id:
        site_ids = db.query(models.GatewayClusterSite.site_id).filter(
            models.GatewayClusterSite.cluster_id == cluster_id
        ).all()
        site_ids = [site_id[0] for site_id in site_ids]
        query = query.filter(models.GatewaySiteConfig.id.in_(site_ids))

    # 添加站点状态过滤条件
    if site_status is not None:
        query = query.filter(models.GatewaySiteConfig.site_status == site_status)

    # 执行统计
    total_sites = query.count()
    enabled_sites = query.filter(models.GatewaySiteConfig.site_status == 1).count()
    disabled_sites = query.filter(models.GatewaySiteConfig.site_status == 0).count()

    return {
        "total_sites": total_sites,
        "active_sites": enabled_sites,
        "inactive_sites": disabled_sites
    }


def get_cluster_statistics(db: Session):
    clusters = db.query(models.GatewayClusterConfig).all()
    cluster_stats = []

    for cluster in clusters:
        site_count = db.query(models.GatewayClusterSite).filter(
            models.GatewayClusterSite.cluster_id == cluster.id
        ).count()
        cluster_stats.append((cluster.name, site_count))

    return cluster_stats


def get_site_list(
    db: Session,
    skip: int = None,
    limit: int = None,
    cluster_id: str = None,
    site_status: int = None,
    for_excel: bool = False  # 添加参数区分是否用于Excel导出
):
    query = db.query(
        models.GatewaySiteConfig,
        models.GatewayClusterConfig.name.label('cluster_name')
    ).join(
        models.GatewayClusterSite,
        models.GatewaySiteConfig.id == models.GatewayClusterSite.site_id
    ).join(
        models.GatewayClusterConfig,
        models.GatewayClusterSite.cluster_id == models.GatewayClusterConfig.id
    ).order_by(models.GatewaySiteConfig.order)

    # 添加查询条件
    if cluster_id:
        query = query.filter(models.GatewayClusterConfig.id == cluster_id)
    if site_status is not None:
        query = query.filter(models.GatewaySiteConfig.site_status == site_status)

    # 添加排序
    query = query.order_by(models.GatewaySiteConfig.order)

    # 获取总记录数
    total = query.count()

    # 如果指定了分页参数，则进行分页
    if skip is not None and limit is not None:
        query = query.offset(skip).limit(limit)
    sites = query.all()

    site_list = []
    for idx, (site, cluster_name) in enumerate(sites, skip + 1 if skip else 1):
        installed_date = None
        if site.installed_date:
            try:
                installed_date = datetime.fromtimestamp(
                    int(site.installed_date) / 1000
                ).strftime('%Y-%m-%d')
            except (ValueError, TypeError):
                installed_date = site.installed_date

        if for_excel:
            # Excel导出使用中文字段名
            site_list.append({
                '序号': idx,
                '群组名称': cluster_name,
                '站点名称': site.name,
                '站点状态': SITE_STATUS_DICT.get(site.site_status, '未知'),
                '站点分类': SITE_CAT_DICT.get(str(site.site_cat), '未知'),
                '站点上线日期': installed_date,
                '排序字段': site.order
            })
        else:
            # API返回使用数据库字段名
            site_list.append({
                'id': site.id,
                'index': idx,
                'cluster_name': cluster_name,
                'name': site.name,
                'site_status': site.site_status,
                'site_status_name': SITE_STATUS_DICT.get(site.site_status, '未知'),
                'site_cat': site.site_cat,
                'site_cat_name': SITE_CAT_DICT.get(str(site.site_cat), '未知'),
                'installed_date': installed_date,
                'order': site.order
            })

    return {"count": total, "list": site_list} if skip is not None else site_list


def get_cluster_name_by_site_id(db: Session, site_id: str) -> str:
    """根据站点ID获取群组名称"""
    result = db.query(models.GatewayClusterConfig.name)\
        .join(models.GatewayClusterSite, models.GatewayClusterConfig.id == models.GatewayClusterSite.cluster_id)\
        .filter(models.GatewayClusterSite.site_id == site_id)\
        .first()
    return result[0] if result else ""

def get_all_clusters(db: Session):
    """获取所有群组信息"""
    return db.query(models.GatewayClusterConfig).all()

def get_cluster_sites(db: Session, cluster_id: str):
    """获取群组下的所有站点ID"""
    sites = db.query(models.GatewayClusterSite.site_id)\
        .filter(models.GatewayClusterSite.cluster_id == cluster_id)\
        .all()
    return [site[0] for site in sites]


def get_cluster_by_id(db: Session, cluster_id: str):
    """根据ID获取群组信息"""
    return db.query(models.GatewayClusterConfig).filter(
        models.GatewayClusterConfig.id == cluster_id
    ).first()


def get_clusters_by_sites(db: Session, site_ids: List[str]) -> Dict[str, Dict[str, str]]:
    """根据站点ID列表获取对应的群组信息"""
    results = db.query(
        models.GatewayClusterSite.site_id,
        models.GatewayClusterConfig.id.label('cluster_id'),
        models.GatewayClusterConfig.name.label('cluster_name'),
        models.GatewaySiteConfig.name.label('site_name')  # 关联站点名称
    ).join(
        models.GatewayClusterConfig,
        models.GatewayClusterSite.cluster_id == models.GatewayClusterConfig.id
    ).join(
        models.GatewaySiteConfig,  # 关联站点信息表
        models.GatewayClusterSite.site_id == models.GatewaySiteConfig.id
    ).filter(
        models.GatewayClusterSite.site_id.in_(site_ids)
    ).all()

    return {
        result.site_id: {
            "site_name": result.site_name,
            "cluster_id": result.cluster_id,
            "cluster_name": result.cluster_name
        }
        for result in results
    }